SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE dbo.[DnnProduct_AutomaticStuckOrderHandling_PW10StuckOrderCheck]
	@LastOrderAgeMin INT,
	@LastOrderAgeMax INT,
	@InvoiceStatusDTSDelta INT,
	@ExcludeList NVARCHAR(1000)
AS
BEGIN
	
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#DBNameTable') AND type in (N'U'))
		DROP TABLE #DBNameTable
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#PWOrderStatuses') AND type in (N'U'))
		DROP TABLE #PWOrderStatuses
	
	CREATE TABLE #DBNameTable (DatabaseName VARCHAR(255))
	CREATE TABLE #PWOrderStatuses (Code INT, OrderStatusId INT)
	
	DECLARE @sql NVARCHAR(MAX)
	
	SET @sql = N'WITH ItemRN AS (SELECT [Name] FROM sys.databases
		WHERE [Name] NOT LIKE ''%cap%''
		and [Name] NOT LIKE ''%demo%''
		and [Name] NOT LIKE ''%dnn%''
		and [Name] NOT LIKE ''%dev%''
		and [Name] NOT LIKE ''%staging%''
		and [Name] NOT LIKE ''%sync%''
		and [Name] NOT LIKE ''%temp%''
		and [Name] NOT LIKE ''%test%''
		and [Name] NOT LIKE ''%backup%''
		and [Name] NOT LIKE ''%upgrade%''
		and [Name] NOT LIKE ''%acs%''
        and [Name] <> ''model''
		and [Name] <> ''PWRewrite''
		and [Name] <> ''adept'''
	
	IF (LEN(@ExcludeList) > 0)
	BEGIN
		SET @sql = @sql + ' and ([Name] NOT IN (' + @ExcludeList + '))'
	END
	
	SET @sql = @sql + N' ) INSERT INTO #DBNameTable (DatabaseName) SELECT [Name] FROM ItemRN ORDER BY [Name];'
	EXECUTE sp_executesql @sql
	
	DECLARE c CURSOR READ_ONLY
	FOR SELECT DatabaseName FROM #DBNameTable

	DECLARE @db NVARCHAR(255)
	
	DECLARE @IsPW10 INT
	
	DECLARE @LastOrderDateTime DATETIME
	DECLARE @LastOrderStatusCode INT
	DECLARE @OutputMessage VARCHAR(2048)
	
	DECLARE @StoreURL NVARCHAR(MAX)
	DECLARE @StoreAdminURL NVARCHAR(MAX)
	DECLARE @OrderNumber INT
	
	DECLARE @LastOrderAge INT

	OPEN c
	FETCH NEXT FROM c INTO @db
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		SET @sql = ''
		SET @IsPW10 = 0
	
		SET @LastOrderDateTime = NULL
		SET @LastOrderStatusCode = NULL
		SET @OutputMessage = ''
		
		SET @StoreURL = ''
		SET @StoreAdminURL = ''
		SET @OrderNumber = NULL
		
		SET @LastOrderAge = NULL
	
		SET @sql = 'SELECT @IsPW10R = COUNT(*) FROM ' + @db + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''cw_Settings_Server'''
		EXECUTE sp_executesql @sql, N'@IsPW10R INT OUTPUT', @IsPW10R = @IsPW10 OUTPUT;
		IF (@IsPW10 > 0)
		BEGIN
			SET @sql = 'SELECT @StoreURLR = FieldValue FROM ' + @db + '..ac_StoreSettings WHERE FieldName = ''Store_StoreUrl'''
			EXECUTE sp_executesql @sql, N'@StoreURLR NVARCHAR(max) OUTPUT', @StoreURLR = @StoreURL OUTPUT
			
			SET @sql = 'SET NOCOUNT ON
						
						DELETE FROM #PWOrderStatuses

						INSERT INTO #PWOrderStatuses
						SELECT TOP 1 1, OrderStatusId FROM ' + @db + '..ac_OrderStatuses WHERE Name = ''Pending Transmission to Backend''
						
						INSERT INTO #PWOrderStatuses
						SELECT TOP 1 2, OrderStatusId FROM ' + @db + '..ac_OrderStatuses WHERE Name = ''Pending Invoice Creation''
						
						INSERT INTO #PWOrderStatuses
						SELECT TOP 1 3, OrderStatusId FROM ' + @db + '..ac_OrderStatuses WHERE Name = ''Shipment Pending''
						
						SELECT TOP 1 @LastOrderDateTimeR = OrderDate, @OrderNumberR = o.OrderNumber, @LastOrderStatusCodeR = os.Code FROM ' + @db + '..ac_Orders o WITH (NOLOCK)
						JOIN #PWOrderStatuses os ON o.OrderStatusId = os.OrderStatusId
						ORDER BY os.Code, o.OrderDate'
			
			EXECUTE sp_executesql @sql, N'@LastOrderDateTimeR DATETIME OUTPUT, @OrderNumberR INT OUTPUT, @LastOrderStatusCodeR INT OUTPUT', @LastOrderDateTimeR = @LastOrderDateTime OUTPUT, @OrderNumberR = @OrderNumber OUTPUT, @LastOrderStatusCodeR = @LastOrderStatusCode OUTPUT
			
			SET @StoreAdminURL = @StoreURL
			
			IF (CHARINDEX('http://',@StoreAdminURL) > 0)
			BEGIN
				SET @StoreAdminURL = REPLACE(@StoreAdminURL, 'http://', 'https://')
			END
			ELSE
			BEGIN
				SET @StoreAdminURL = 'http://' + @StoreAdminURL
			END
			
			IF (RIGHT(@StoreAdminURL, 1) <> '/')
			BEGIN
				SET @StoreAdminURL = @StoreAdminURL + '/Admin/'
			END
			ELSE
			BEGIN
				SET @StoreAdminURL = @StoreAdminURL + 'Admin/'
			END

			IF (@LastOrderDateTime IS NOT NULL)
			BEGIN
				IF (@LastOrderStatusCode < 3)
				BEGIN
					SET @LastOrderAge = DATEDIFF(d, @LastOrderDateTime, GETDATE())
					IF (@LastOrderAge >= @LastOrderAgeMin and @LastOrderAge <= @LastOrderAgeMax)
					BEGIN
						IF (@LastOrderStatusCode = 2)
						BEGIN
							SET @sql = 'UPDATE ' + @db + '..cw_MaxDts SET InvoiceStatusDTS = DATEADD(m, -@InvoiceStatusDTSDelta, GETDATE())'
							EXECUTE sp_executesql @sql, N'@InvoiceStatusDTSDelta INT', @InvoiceStatusDTSDelta
							SET @OutputMessage = 'SYNC'
							INSERT INTO DnnProduct_AutomaticStuckOrderHandling_LastOrderStatuses([Name], LastOrderDateTime, LastOrderStatusCode, [Action], [Timestamp], [StoreURL], [StoreAdminURL], [OrderNumber], [PWVersion])
							VALUES (@db, @LastOrderDateTime, @LastOrderStatusCode, @OutputMessage, GETDATE(), @StoreURL, @StoreAdminURL, @OrderNumber, 'PW10')
						END
						IF (@LastOrderStatusCode = 1)
						BEGIN
							SET @OutputMessage = 'RESEND ORDER'
							SET @StoreAdminURL = @StoreAdminURL + 'Orders/ViewOrder.aspx?OrderNumber={OrderNumber}'
							SET @sql = 'INSERT INTO DnnProduct_AutomaticStuckOrderHandling_LastOrderStatuses([Name], LastOrderDateTime, LastOrderStatusCode, [Action], [Timestamp], [StoreURL], [StoreAdminURL], [OrderNumber], [PWVersion])
										SELECT 
											@db AS [Name], o.OrderDate AS LastOrderDateTime, os.Code AS LastOrderStatusCode, 
											@OutputMessage AS [Action], GETDATE() AS [Timestamp], @StoreURL AS [StoreURL], 
											REPLACE(@StoreAdminURL, ''{OrderNumber}'', o.OrderNumber) AS [StoreAdminURL], o.OrderNumber AS OrderNumber, ''PW10'' AS [PWVersion]
										FROM ' + @db + '..ac_Orders o WITH (NOLOCK) INNER JOIN #PWOrderStatuses os ON o.OrderStatusId = os.OrderStatusId
										WHERE os.Code = @LastOrderStatusCode
										ORDER BY os.Code, o.OrderDate'
										
							EXECUTE sp_executesql @sql, N'@LastOrderStatusCode INT, @db NVARCHAR(255), @OutputMessage VARCHAR(2048), @StoreURL NVARCHAR(MAX), @StoreAdminURL NVARCHAR(MAX)', @LastOrderStatusCode, @db, @OutputMessage, @StoreURL, @StoreAdminURL
						END
					END
				END
			END
		END

		FETCH NEXT FROM c INTO @db
	END

	CLOSE c
	DEALLOCATE c

	DROP TABLE #DBNameTable
	DROP TABLE #PWOrderStatuses
END

GO
